package com.hy.dao.impl;

import com.hy.Vo.PurchaseApplyDeptUserVo;
import com.hy.Vo.PurchaseApplyDetailGoodsVo;
import com.hy.bean.PurchaseApply;
import com.hy.dao.PurchaseApplyDao;
import com.hy.utils.DBManager;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.sql.Connection;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.List;

public class PurchaseAppIyDaoImpl implements PurchaseApplyDao {

    private final QueryRunner queryRunner = new QueryRunner();

    @Override
    public PurchaseApply queryById() {

        return null;
    }

    @Override
    public PurchaseApply queryByPaId(Integer id) {
        Connection connection = null;
        String sql = "select id,dept_id deptId,consumer_id consumerId,apply_time applyTime,uses uses,remark remark,status status from purchase_apply where id = ?";

        try {
            connection = DBManager.getConnection();
            return queryRunner.query(connection, sql, new BeanHandler<>(PurchaseApply.class),id);
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            try {
                DbUtils.close(connection);
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }

    @Override
    public List<PurchaseApplyDeptUserVo> queryMap(String deptName, String username) {
        Connection connection = null;
        String sql = "select pa.id,pa.dept_id deptId,dept.dept_name deptName,pa.consumer_id consumerId,cu.username username,pa.apply_time applyTime,pa.uses uses,pa.remark remark,pa.status status from purchase_apply pa\n" +
                "    inner join department dept on pa.dept_id = dept.id\n" +
                "    inner join consumer cu on pa.consumer_id = cu.id";

        try {
            connection = DBManager.getConnection();
            List<PurchaseApplyDeptUserVo> listMap = null;
            if (!deptName.equals("null") && !username.equals("null")) {
                sql = sql + " where dept.dept_name = ? and cu.username = ?";
                listMap = queryRunner.query(connection, sql, new BeanListHandler<>(PurchaseApplyDeptUserVo.class), deptName, username);
            } else if (!deptName.equals("null")) {
                sql = sql + " where dept.dept_name = ?";
                listMap = queryRunner.query(connection, sql, new BeanListHandler<>(PurchaseApplyDeptUserVo.class), deptName);
            } else if (!username.equals("null")) {
                sql = sql + " where cu.username = ?";
                listMap = queryRunner.query(connection, sql, new BeanListHandler<>(PurchaseApplyDeptUserVo.class),username);
            }else {
                listMap = queryRunner.query(connection, sql, new BeanListHandler<>(PurchaseApplyDeptUserVo.class));
            }
            return childrenObject(listMap);
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            try {
                DbUtils.close(connection);
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }

    private List<PurchaseApplyDeptUserVo> childrenObject(List<PurchaseApplyDeptUserVo> listMap) {
        Connection connection = null;
        String sql = "select pad.id id,pad.purchase_apply_id purchaseApplyId,pad.goods_id goodsId,pad.goods_count goodsCount,pad.market_price marketPrice,pad.trade_price tradePrice,pad.purchase_price purchasePrice,pad.money,pad.required_time requiredTime,g.goodsname goodsname from purchase_apply_detail pad inner join goods g on pad.goods_id = g.id where purchase_apply_id = ?";
        try {
            connection = DBManager.getConnection();
            for (int i = 0; i < listMap.size(); i++) {
                listMap.get(i).setChildren(queryRunner.query(connection, sql, new BeanListHandler<>(PurchaseApplyDetailGoodsVo.class), listMap.get(i).getId()));
            }
            return listMap;
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            try {
                DbUtils.close(connection);
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }

    @Override
    public long count(PurchaseApply purchaseApply) {
        return 0;
    }

    @Override
    public int insert(PurchaseApply purchaseApply) {
        Connection connection = null;
        String sql = "INSERT INTO purchase_apply values(seq_purchase_apply.nextval,?,?,to_date(?,'yyyy-mm-dd'),?,?,1)";
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        try {
            connection = DBManager.getConnection();
            return queryRunner.update(connection, sql, purchaseApply.getDeptId(), purchaseApply.getConsumerId(), sdf.format(purchaseApply.getApplyTime()), purchaseApply.getUses(), purchaseApply.getRemark());
        }catch (Exception e){
            throw new RuntimeException(e);
        }finally {
            try {
                DbUtils.close(connection);
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }

    @Override
    public int insertBatch(List<PurchaseApply> entities) {
        return 0;
    }

    @Override
    public int insertOrUpdateBatch(List<PurchaseApply> entities) {
        return 0;
    }

    @Override
    public int update(PurchaseApply purchaseApply) {
        Connection connection = null;
        String sql = "update purchase_apply set dept_id = ?,consumer_id = ?,apply_time = to_date(?,'yyyy-mm-dd'),uses = ?,remark = ?,status = ? where id = ?";
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        try {
            connection = DBManager.getConnection();
            return queryRunner.update(connection, sql, purchaseApply.getDeptId(), purchaseApply.getConsumerId(), sdf.format(purchaseApply.getApplyTime()), purchaseApply.getUses(), purchaseApply.getRemark(), purchaseApply.getStatus(), purchaseApply.getId());
        }catch (Exception e){
            throw new RuntimeException(e);
        }finally {
            try {
                DbUtils.close(connection);
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }

    @Override
    public int updateStatus(Connection connection,Integer id, Integer status) throws SQLException {
        String sql = "update purchase_apply set status = ? where id = ?";
            return queryRunner.update(connection, sql, status, id);
    }

    @Override
    public int deleteById(Connection connection, Integer id) throws SQLException {
        String sql = "delete from purchase_apply where id = ?";
        return queryRunner.update(connection, sql, id);
    }
}
